using System;
using System.Collections.Generic;
using System.Text;

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;

namespace crudReportor
{
    class crudReportor
    {
        private String sqlScript;
        private TDbVendor dbvendor;
        public crudReportor(String sqlScript, TDbVendor dbvendor)
        {
            this.sqlScript = sqlScript;
            this.dbvendor = dbvendor;
        }

        private String msg;

        public String report()
        {
            TGSqlParser sqlparser = new TGSqlParser(this.dbvendor);
            sqlparser.SqlText.Text = sqlScript;

            msg = "";
            int ret = sqlparser.Parse();
            if (ret == 0)
            {
                for (int i = 0; i < sqlparser.SqlStatements.Count(); i++)
                {
                    analyzeStatement(sqlparser.SqlStatements[i],0);
                }
            }
            else
            {
                msg = sqlparser.ErrorMessages;
            }

            return msg;
        }

        private String getColumnInTable(TCustomSqlStatement pSql, TLzTable pTable)
        {
            TSourceTokenList tokenList = pSql.TableTokens;

            TSourceToken st = null;
            string tablestr = null;
            string ret = null;

            for (int i = 0; i < tokenList.Count(); i++)
            {
                st = tokenList[i];
                tablestr = st.AsText;

                if (st.ParentToken != null)
                {
                    //schema
                    tablestr = st.ParentToken.AsText + "." + tablestr;

                    if (st.ParentToken.ParentToken != null)
                    {
                        //database
                        tablestr = st.ParentToken.ParentToken.AsText + "." + tablestr;
                    }
                }

                if (!(pTable.TableFullname.CompareTo(tablestr) == 0)) continue;

                
                ret = printColumnsInTableToken(st);
                break;
            }

            return ret;

        }

        private string printColumnsInTableToken(TSourceToken st)
        {
            string ret = "(columns: ";

            if (st.RelatedToken != null)
            {
                // declared table alias token
                TSourceToken rt = st.RelatedToken;
                TSourceToken rrt = null;
                for (int i = 0; i < rt.RelatedTokens.Count(); i++)
                {
                    rrt = rt.RelatedTokens[i];
                    if (rrt.ChildToken != null)
                    {
                        ret = ret + rrt.ChildToken.AsText + ",";
                    }
                }
            }

            TSourceToken rtt = null;
            for (int i = 0; i < st.RelatedTokens.Count(); i++)
            {
                // reference table token
                rtt = st.RelatedTokens[i];
                if (rtt.DBObjType == TDBObjType.ttObjField)
                {
                    // get all field tokens link with table token (those token not linked by syntax like tablename.fieldname)
                    // but like this : select f from t

                    ret = ret + rtt.AsText + ",";
                }
                if (rtt.ChildToken != null)
                {
                    ret = ret + rtt.ChildToken.AsText + ",";
                }
            }

            return ret+")";
        }


        private void analyzeStatement(TCustomSqlStatement sql, int level)
        {
            String ident = new string((char)9, level);
            switch (sql.SqlStatementType)
            {
                case TSqlStatementType.sstCreateTable:
                    msg = msg + Environment.NewLine+ ident + "Create table: "+sql.Table.TableName;
                    msg = msg + Environment.NewLine + ident + getColumnInTable(sql, sql.Table);
                    break;
                case TSqlStatementType.sstSelect:
                    msg = msg + Environment.NewLine + ident + "Select:";
                    for (int j = 0; j < sql.Tables.Count(); j++)
                    {
                        if (sql.Tables[j].TableType == TLzTableType.lttAttr)
                        {
                            msg = msg + Environment.NewLine + ident + sql.Tables[j].TableName;
                            msg = msg + Environment.NewLine + ident + getColumnInTable(sql, sql.Tables[j]) + Environment.NewLine;
                        }
                    }
                    break;
                case TSqlStatementType.sstUpdate:
                    msg = msg + Environment.NewLine+ident + "Update:";
                    for (int j = 0; j < sql.Tables.Count(); j++)
                    {
                        if (sql.Tables[j].TableType == TLzTableType.lttAttr)
                        {
                            msg = msg + sql.Tables[j].TableName + ", ";
                        }
                    }
                    break;
                case TSqlStatementType.sstDelete:
                    msg = msg + Environment.NewLine+ident + "Delete:";
                    for (int j = 0; j < sql.Tables.Count(); j++)
                    {
                        if (sql.Tables[j].TableType == TLzTableType.lttAttr)
                            msg = msg  + sql.Tables[j].TableName + ", ";
                    }
                    break;
                case TSqlStatementType.sstInsert:
                    msg = msg + Environment.NewLine+ident + "Insert:";
                    for (int j = 0; j < sql.Tables.Count(); j++)
                    {
                        if (sql.Tables[j].TableType == TLzTableType.lttAttr)
                            msg = msg + sql.Tables[j].TableName + ", ";
                    }
                    break;
                case TSqlStatementType.sstMssqlCreateProcedure:
                case TSqlStatementType.sstMssqlAlterProcedure:
                    TMssqlCreateProcedure procedure = (TMssqlCreateProcedure)(sql);
                    msg = msg + Environment.NewLine + ident + sql.SqlStatementType + String.Format("(proc: {0} - {1})", procedure.procedurename.AsText,(sql.SqlStatementType == TSqlStatementType.sstMssqlCreateProcedure)?"create":"alter");
                    break;
                case TSqlStatementType.sstMssqlCreateFunction:
                case TSqlStatementType.sstMssqlAlterFunction:
                    TMssqlCreateFunction function = (TMssqlCreateFunction)(sql);
                    msg = msg + Environment.NewLine + ident + sql.SqlStatementType + String.Format("(func: {0} - {1})", function.functionname.AsText, (sql.SqlStatementType == TSqlStatementType.sstMssqlCreateFunction) ? "create" : "alter");
                    break;
                case TSqlStatementType.sstMssqlDropTable:
                    TMssqlDropTable dropTable = (TMssqlDropTable)sql;
                    msg = msg + Environment.NewLine + ident + sql.SqlStatementType + String.Format("table: {0} - drop",dropTable.ndTable.AsText);
                    break;
                default:
                    msg = msg + Environment.NewLine + ident + sql.SqlStatementType;
                    break;
            }

            for (int i = 0; i < sql.ChildNodes.Count(); i++)
            {
                if (sql.ChildNodes[i] is TCustomSqlStatement)
                {
                    analyzeStatement((TCustomSqlStatement)sql.ChildNodes[i],level+1);
                }
            }

        }

    }
}
